﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace FTP_progress
{
    public class Mysql
    {
        private readonly string _connectionString;
        public Mysql(string server, string database, string uid, string password)
        {
            _connectionString = $"Server={server};Database={database};Uid={uid};Pwd={password};Charset=utf8mb4;";
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <returns>MySqlConnection 对象</returns>
        private MySqlConnection OpenConnection()
        {
            var connection = new MySqlConnection(_connectionString);
            connection.Open();
            return connection;
        }

        /// <summary>
        /// 通用方法：执行非查询命令（增、删、改）
        /// </summary>
        /// <param name="query">SQL 查询语句</param>
        /// <param name="parameters">查询参数</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string query, Dictionary<string, object> parameters = null)
        {
            using (var connection = OpenConnection())
            using (var command = new MySqlCommand(query, connection))
            {
                if (parameters != null)
                {
                    foreach (var param in parameters)
                    {
                        command.Parameters.AddWithValue(param.Key, param.Value);
                    }
                }
                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 通用方法：执行查询并返回结果
        /// </summary>
        /// <param name="query">SQL 查询语句</param>
        /// <param name="parameters">查询参数</param>
        /// <returns>查询结果 DataTable</returns>
        public DataTable ExecuteQuery(string query, Dictionary<string, object> parameters = null)
        {
            using (var connection = OpenConnection())
            using (var command = new MySqlCommand(query, connection))
            {
                if (parameters != null)
                {
                    foreach (var param in parameters)
                    {
                        command.Parameters.AddWithValue(param.Key, param.Value);
                    }
                }
                using (var adapter = new MySqlDataAdapter(command))
                {
                    var dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    return dataTable;
                }
            }
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="data">数据字典（列名和值）</param>
        public void Insert(string tableName, Dictionary<string, object> data)
        {
            var columns = string.Join(", ", data.Keys);
            var placeholders = string.Join(", ", data.Keys.Select(key => "@" + key));
            var query = $"INSERT INTO {tableName} ({columns}) VALUES ({placeholders})";
            ExecuteNonQuery(query, data);
        }
        /// <summary>
        /// 条件删除
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="whereClause">WHERE 条件子句</param>
        /// <param name="parameters">参数</param>
        public void Delete(string tableName, string whereClause, Dictionary<string, object> parameters = null)
        {
            var query = $"DELETE FROM {tableName} WHERE {whereClause}";
            ExecuteNonQuery(query, parameters);
        }

        /// <summary>
        /// 清空表
        /// </summary>
        /// <param name="tableName">表名</param>
        public void DeleteAll(string tableName)
        {
            var query = $"DELETE FROM {tableName}";
            ExecuteNonQuery(query);
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="whereClause">WHERE 条件子句</param>
        /// <param name="parameters">参数</param>
        /// <returns>DataTable 结果</returns>
        public DataTable Select(string tableName, string whereClause = null, Dictionary<string, object> parameters = null)
        {
            var query = $"SELECT * FROM {tableName}";
            if (!string.IsNullOrEmpty(whereClause))
            {
                query += $" WHERE {whereClause}";
            }
            return ExecuteQuery(query, parameters);
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="data">要更新的数据</param>
        /// <param name="whereClause">WHERE 条件子句</param>
        /// <param name="parameters">WHERE 条件参数</param>
        public void Update(string tableName, Dictionary<string, object> data, string whereClause, Dictionary<string, object> parameters = null)
        {
            var setClause = string.Join(", ", data.Keys.Select(key => $"{key} = @{key}"));
            var query = $"UPDATE {tableName} SET {setClause} WHERE {whereClause}";

            // 合并参数
            var allParameters = new Dictionary<string, object>();
            foreach (var param in data)
            {
                allParameters.Add($"@{param.Key}", param.Value);
            }

            if (parameters != null)
            {
                foreach (var param in parameters)
                {
                    allParameters.Add(param.Key, param.Value);
                }
            }

            ExecuteNonQuery(query, allParameters);
        }
    }
}